﻿using ExcelDna.Integration;
using static ExcelDna.Integration.XlCall;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
namespace 字符串处理
{
    public class StringUtility

    {
        [ExcelFunction(IsMacroType = true,Description = "字符串拼接函数，StringJoinRange：传入的需要写入目标单元格的数据区域，strSplit：用来分割的字符")]
        //AllowReference = true把传进来的EXCEL区域识别出来。
        public static string StringJoin(
            [ExcelArgument(Description = "输入要拼接的字符串区域", AllowReference = true)] object StringJoinRange,
            [ExcelArgument(Description = "输入分隔字符串")] string strSplit)
        {
            Excel(xlfVolatile, false);//取消易失性
            if (!(StringJoinRange is ExcelReference))
                return ExcelError.ExcelErrorName.ToString();
            //拿到EXCEL表的名称，工作薄[工作表] 的格式
            object targetSheetName = Excel(xlSheetNm, StringJoinRange);

            //拿到已使用区域的行列序号，从0开始
            int firstRow = (int)(double)Excel(xlfGetDocument, 9, targetSheetName) - 1;
            int lastRow = (int)(double)Excel(xlfGetDocument, 10, targetSheetName) - 1;
            int firstCol = (int)(double)Excel(xlfGetDocument, 11, targetSheetName) - 1;
            int lastCol = (int)(double)Excel(xlfGetDocument, 12, targetSheetName) - 1;
            //类型转换为ExcelReference，前面已经判断过是ExcelReference类型，不是的话程序会退出。
            ExcelReference targetRef = (ExcelReference)StringJoinRange;

            //拿到传入区域和已使用区域之间的交集
            ExcelReference usedTarget = new ExcelReference(
                    rowFirst: firstRow>targetRef.RowFirst? firstRow: targetRef.RowFirst,
                    rowLast: lastRow<targetRef.RowLast? lastRow: targetRef.RowLast,
                    columnFirst: firstCol>targetRef.ColumnFirst? firstCol: targetRef.ColumnFirst,
                    columnLast: lastCol<targetRef.ColumnLast? lastCol: targetRef.ColumnLast,
                    sheetId: targetRef.SheetId);
            //ExcelReference提取值内容放入object对象，再供下一步判断是只有一个单元格还是多个，多个时为object[,]
            object values = usedTarget.GetValue();


            if (!(values is object[,]))
            {
                if (values is ExcelEmpty)
                {
                    return "";
                }
                else
                {
                    return values.ToString();
                }
                
            }
            else
            {
                List<string> list = new List<string>();
                object[,] valuesArr = (object[,])values;
                
                for (int i = 0; i < valuesArr.GetLength(0); i++)
                {
                    for (int j = 0; j < valuesArr.GetLength(1); j++)
                    {
                        if (!(valuesArr.GetValue(i, j) is ExcelEmpty))
                        {
                            list.Add(valuesArr.GetValue(i, j).ToString());
                        }
                    }
                }

                return string.Join(strSplit, list);
            }
            
        }

        [ExcelFunction(IsMacroType =true,Description = "字符串拼接函数，在查找的区域查找对应条件下的值，最终用分隔符拼接起来，类似系统函数SUMIF、COUNTIF")]
        public static string StringJoinIf(
    [ExcelArgument(Description = "查找的区域",AllowReference =true)] object lookupRange,
    [ExcelArgument(Description = "用于验证查找区域是否符合的条件")] string criteria,
    [ExcelArgument(Description = "要拼接的字符串区域", AllowReference = true)] object StringJoinRange,
    [ExcelArgument(Description = "输入分隔字符串")] string strSplit,
    [ExcelArgument(Description = "是否精确匹配，默认为否")] bool isExactMatch = false)
        {
            Excel(xlfVolatile, false);//取消易失性
            if (!(StringJoinRange is ExcelReference))
                return "错误！查找区域输入有误";

            if (!(StringJoinRange is ExcelReference))
                return "错误！拼接区域输入有误";

            object targetSheetName = Excel(xlSheetNm, lookupRange);

            int firstRow = (int)(double)Excel(xlfGetDocument, 9, targetSheetName) - 1;
            int lastRow = (int)(double)Excel(xlfGetDocument, 10, targetSheetName) - 1;
            int firstCol = (int)(double)Excel(xlfGetDocument, 11, targetSheetName) - 1;
            int lastCol = (int)(double)Excel(xlfGetDocument, 12, targetSheetName) - 1;

            ExcelReference lookupRangeRef = (ExcelReference)lookupRange;

            ExcelReference usedLookupRange = new ExcelReference(
                    rowFirst: firstRow > lookupRangeRef.RowFirst ? firstRow : lookupRangeRef.RowFirst,
                    rowLast: lastRow < lookupRangeRef.RowLast ? lastRow : lookupRangeRef.RowLast,
                    columnFirst: firstCol > lookupRangeRef.ColumnFirst ? firstCol : lookupRangeRef.ColumnFirst,
                    columnLast: lastCol < lookupRangeRef.ColumnLast ? lastCol : lookupRangeRef.ColumnLast,
                    sheetId: lookupRangeRef.SheetId);

            object lookupRangeValues = usedLookupRange.GetValue();

            //StringJoinRange区域转换
            ExcelReference StringJoinRangeRef = (ExcelReference)StringJoinRange;

            ExcelReference usedStringJoinRangeRange = new ExcelReference(
                    rowFirst: usedLookupRange.RowFirst,
                    rowLast: usedLookupRange.RowLast,
                    columnFirst: firstCol > StringJoinRangeRef.ColumnFirst ? firstCol : StringJoinRangeRef.ColumnFirst,
                    columnLast: lastCol < StringJoinRangeRef.ColumnLast ? lastCol : StringJoinRangeRef.ColumnLast,
                    sheetId: StringJoinRangeRef.SheetId);

            object StringJoinRangeValues = usedStringJoinRangeRange.GetValue();


            if (!(lookupRangeValues is object[,]))
            {
                if (lookupRangeValues is ExcelEmpty)
                {
                    return "";
                }
                else
                {
                    return StringJoinRange.ToString();
                }
                
            }
            else
            {
                List<string> list = new List<string>();

                object[,] lookupRangeValuesArr = (object[,])lookupRangeValues;
                object[,] StringJoinRangeValuesArr = (object[,])StringJoinRangeValues;

                    for (int i = 0; i < lookupRangeValuesArr.GetLength(0); i++)
                    {
                        if (isExactMatch)
                        {
                            if (lookupRangeValuesArr.GetValue(i, 0).ToString() == criteria)
                            {
                                list.Add(StringJoinRangeValuesArr.GetValue(i, 0).ToString());
                            }

                        }
                        else
                        {
                            if (lookupRangeValuesArr.GetValue(i, 0).ToString().Contains(criteria))
                            {
                                list.Add(StringJoinRangeValuesArr.GetValue(i, 0).ToString());
                            }
                        }
                    }

                return string.Join(strSplit, list);

            }

        }


        //--input=输入
        //--pattern=匹配规则
        //--matchNum=确定第几个匹配返回值，索引号从0开始，第1个匹配，传入0
        //--groupNum=确定第几组匹配，索引号从1开始，0为返回上层的match内容。
        //--isCompiled=是否编译，是为1，否为0，暂时没有测试过哪个快在数据量大时，文档好像说数据量大用编译比较好
        //--isECMAScript，用来指定\w是否匹配一些特殊编码之类的例如中文，当false时会匹配中文,指定为true时，可能和其他的指定有些冲突。
        //--RegexOptions.ECMAScript 选项只能与 RegexOptions.IgnoreCase 和 RegexOptions.Multiline 选项结合使用。在正则表达式中使用其他选项会导致 ArgumentOutOfRangeException。
        //--isRightToLeft，从右往左匹配。
        //--returnNum，反回split数组中的第几个元素，索引从0开始

        [ExcelFunction(Description = "正则匹配组，Pattern里传入（）来分组")]

        public static string RegexMatchGroup(
           [ExcelArgument(Description = "输入的字符串")] string input,
           [ExcelArgument(Description = "匹配规则")] string pattern,
           [ExcelArgument(Description = "确定第几个匹配返回值，索引号从0开始，第1个匹配，传入0，默认为0")] int matchNum = 0,
           [ExcelArgument(Description = "确定第几组匹配，索引号从1开始，0为返回上层的match内容，默认为1")] int groupNum = 1,
           [ExcelArgument(Description = "是否编译，是为1，否为0，暂时没有测试过哪个快在数据量大时，文档好像说数据量大用编译比较好，默认为false")] bool isCompiled = false,
           [ExcelArgument(Description = @"用来指定\w是否匹配一些特殊编码之类的例如中文，当false时会匹配中文,指定为true时，可能和其他的指定有些冲突，默认为false")] bool isECMAScript = false,
           [ExcelArgument(Description = "从右往左匹配，默认为false")] bool isRightToLeft = false)
        {
            try
            {
                RegexOptions options = GetRegexOptions(isCompiled, isECMAScript, isRightToLeft);
                MatchCollection matches = Regex.Matches(input, pattern, options);

                if (matchNum <= matches.Count - 1)
                {
                    Match match = matches[matchNum];
                    if (groupNum == 0)
                    {
                        return match.Value;
                    }
                    else
                    {
                        if (groupNum < match.Groups.Count)
                        {
                            return match.Groups[groupNum].Value;
                        }
                        else
                        {
                            return "";
                        }
                    }
                }
                else
                {
                    return "";
                }

            }
            catch (ArgumentOutOfRangeException)
            {
                return "Options错误";
            }
            catch (ArgumentNullException)
            {
                return "";
            }

            catch (ArgumentException)
            {
                return "Pattern错误";
            }
            catch (Exception)
            {
                return "";
            }
        }

        [ExcelFunction(Description = "正则匹配，不含Group组匹配")]
        public static string RegexMatch(
           [ExcelArgument(Description = "输入的字符串")] string input,
           [ExcelArgument(Description = "匹配规则")] string pattern,
           [ExcelArgument(Description = "确定第几个匹配返回值，索引号从0开始，第1个匹配，传入0，默认为0")] int matchNum = 0,
           [ExcelArgument(Description = "是否编译，是为1，否为0，暂时没有测试过哪个快在数据量大时，文档好像说数据量大用编译比较好，默认为false")] bool isCompiled = false,
           [ExcelArgument(Description = @"用来指定\w是否匹配一些特殊编码之类的例如中文，当false时会匹配中文,指定为true时，可能和其他的指定有些冲突，默认为false")] bool isECMAScript = false,
           [ExcelArgument(Description = "从右往左匹配，默认为false")] bool isRightToLeft = false)
        {
            int groupNum = 0;
            return RegexMatchGroup(input, pattern, matchNum, groupNum, isCompiled, isECMAScript, isRightToLeft);
        }

        [ExcelFunction(Description = "正则替换")]
        public static string RegexReplace(
           [ExcelArgument(Description = "输入的字符串")] string input,
           [ExcelArgument(Description = "匹配规则")] string pattern,
           [ExcelArgument(Description = "匹配到的文件替换的字符串，默认为替换为空")] string replacement = "",
           [ExcelArgument(Description = "是否编译，是为1，否为0，暂时没有测试过哪个快在数据量大时，文档好像说数据量大用编译比较好，默认为false")] bool isCompiled = false,
           [ExcelArgument(Description = @"用来指定\w是否匹配一些特殊编码之类的例如中文，当false时会匹配中文,指定为true时，可能和其他的指定有些冲突，默认为false")] bool isECMAScript = false,
           [ExcelArgument(Description = "从右往左匹配，默认为false")] bool isRightToLeft = false)
        {
            try
            {
                RegexOptions options = GetRegexOptions(isCompiled, isECMAScript, isRightToLeft);
                return Regex.Replace(input, pattern, replacement, options);
            }
            catch (ArgumentOutOfRangeException)
            {
                return "Options错误";
            }
            catch (ArgumentNullException)
            {
                return "";
            }

            catch (ArgumentException)
            {
                return "Pattern错误";
            }
            catch (Exception)
            {
                return input;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="input"></param>
        /// <param name="pattern"></param>
        /// <param name="returnNum">索引从0开始</param>
        /// <param name="isCompiled"></param>
        /// <param name="isECMAScript"></param>
        /// <param name="isRightToLeft"></param>
        /// <returns></returns>
        [ExcelFunction(Description = "正则分割")]
        public static string RegexSplit(
           [ExcelArgument(Description = "输入的字符串")] string input,
           [ExcelArgument(Description = "匹配规则")] string pattern,
           [ExcelArgument(Description = "分割后返回第几个项目，索引号从0开始，第1个匹配，传入0，，默认为0")] int returnNum = 0,
           [ExcelArgument(Description = "是否编译，是为1，否为0，暂时没有测试过哪个快在数据量大时，文档好像说数据量大用编译比较好，默认为false")] bool isCompiled = false,
           [ExcelArgument(Description = @"用来指定\w是否匹配一些特殊编码之类的例如中文，当false时会匹配中文,指定为true时，可能和其他的指定有些冲突，默认为false")] bool isECMAScript = false,
           [ExcelArgument(Description = "从右往左匹配，默认为false")] bool isRightToLeft = false)
        {
            try
            {
                RegexOptions options = GetRegexOptions(isCompiled, isECMAScript, isRightToLeft);
                string[] splitResult = Regex.Split(input, pattern, options);
                if (returnNum <= splitResult.Length - 1)
                {
                    return splitResult[returnNum];
                }
                else
                {
                    return "";
                }

            }
            catch (ArgumentOutOfRangeException)
            {
                return "Options错误";
            }
            catch (ArgumentNullException)
            {
                return "";
            }

            catch (ArgumentException)
            {
                return "Pattern错误";
            }
            catch (Exception)
            {
                return input;
            }
        }

        [ExcelFunction(Description = "正则匹配判断")]
        public static bool RegexIsMatch(
           [ExcelArgument(Description = "输入的字符串")] string input,
           [ExcelArgument(Description = "匹配规则")] string pattern,
           [ExcelArgument(Description = "是否编译，是为1，否为0，暂时没有测试过哪个快在数据量大时，文档好像说数据量大用编译比较好，默认为false")] bool isCompiled = false,
           [ExcelArgument(Description = @"用来指定\w是否匹配一些特殊编码之类的例如中文，当false时会匹配中文,指定为true时，可能和其他的指定有些冲突，默认为false")] bool isECMAScript = false,
           [ExcelArgument(Description = "从右往左匹配，默认为false")] bool isRightToLeft = false)
        {
            try
            {
                RegexOptions options = GetRegexOptions(isCompiled, isECMAScript, isRightToLeft);
                return Regex.IsMatch(input, pattern, options);
            }

            catch (Exception)
            {
                return false;
            }
        }

        private static RegexOptions GetRegexOptions(bool isCompiled, bool isECMAScript, bool isRightToLeft)
        {
            List<RegexOptions> listOptions = new List<RegexOptions>();
            if (isCompiled == true)
            {
                listOptions.Add(RegexOptions.Compiled);
            }
            if (isRightToLeft == true)
            {
                listOptions.Add(RegexOptions.RightToLeft);
            }
            if (isECMAScript == true)
            {
                listOptions.Add(RegexOptions.ECMAScript);
            }

            RegexOptions options = new RegexOptions();
            foreach (var item in listOptions)
            {
                if (options == 0)
                {
                    options = item;
                }
                else
                {
                    options = options | item;
                }
            }

            return options;
        }







    }
}
